﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Pub;
using ICSharpCode.TextEditor;
using ICSharpCode.TextEditor.Document;

namespace LZQXT.Test.Forms.DB
{
    public partial class FDBRow : Form
    {

        List<object> lst1 = new List<object>();
        TextBox[] tbox;
        Label[] lab;

        public string sTabName = "";
        public string strSQL_Key = "";
        public DataSet ds1 = null;
        public DataRow dr = null;
        public int i_RowIndex = -1;
        public ADOTest ado1 = null;
        public string s_Enter = "";

        public struct TableKey
        {
            public DataColumn dc;
            public string s_Val;
        }

        public TableKey[] tabKey;

        public TextEditorControl tboxSQL;
        public CTextEditMenu[] textBox_Menu;
        public List<TextEditorControl> lstTextBox = new List<TextEditorControl>();

        public FDBRow()
        {
            InitializeComponent();
        }

        private void FDBRow_Load(object sender, EventArgs e)
        {
            tabControl1.Dock = DockStyle.Fill;
            panel2.Dock = DockStyle.Fill;
            groupBox5.Dock = DockStyle.Fill;

            tabCtrlSQL.Dock = DockStyle.Fill;
            groupBox1.Dock = DockStyle.Fill;
            groupBox2.Dock = DockStyle.Fill;
            groupBox3.Dock = DockStyle.Fill;
            groupBox4.Dock = DockStyle.Fill;

            tbox_Sql_Add.BorderStyle = BorderStyle.Fixed3D;
            tbox_Sql_Add.Document.HighlightingStrategy = HighlightingStrategyFactory.CreateHighlightingStrategy("TSQL");
            tbox_Sql_Add.Encoding = System.Text.Encoding.Default;

            tbox_Sql_Edt.BorderStyle = BorderStyle.Fixed3D;
            tbox_Sql_Edt.Document.HighlightingStrategy = HighlightingStrategyFactory.CreateHighlightingStrategy("TSQL");
            tbox_Sql_Edt.Encoding = System.Text.Encoding.Default;

            tbox_Sql_Del.BorderStyle = BorderStyle.Fixed3D;
            tbox_Sql_Del.Document.HighlightingStrategy = HighlightingStrategyFactory.CreateHighlightingStrategy("TSQL");
            tbox_Sql_Del.Encoding = System.Text.Encoding.Default;

            tbox_Sql_Sel.BorderStyle = BorderStyle.Fixed3D;
            tbox_Sql_Sel.Document.HighlightingStrategy = HighlightingStrategyFactory.CreateHighlightingStrategy("TSQL");
            tbox_Sql_Sel.Encoding = System.Text.Encoding.Default;

            lstTextBox.Clear();
            lstTextBox.Add(tbox_Sql_Add);
            lstTextBox.Add(tbox_Sql_Edt);
            lstTextBox.Add(tbox_Sql_Del);
            lstTextBox.Add(tbox_Sql_Sel);

            textBox_Menu = new CTextEditMenu[4];
            textBox_Menu[0] = new CTextEditMenu(tbox_Sql_Add);
            textBox_Menu[1] = new CTextEditMenu(tbox_Sql_Edt);
            textBox_Menu[2] = new CTextEditMenu(tbox_Sql_Del);
            textBox_Menu[3] = new CTextEditMenu(tbox_Sql_Sel);

            tbox_Sql_Add.ActiveTextAreaControl.TextArea.KeyDown += tbox_KeyDown;
            tbox_Sql_Edt.ActiveTextAreaControl.TextArea.KeyDown += tbox_KeyDown;
            tbox_Sql_Del.ActiveTextAreaControl.TextArea.KeyDown += tbox_KeyDown;
            tbox_Sql_Sel.ActiveTextAreaControl.TextArea.KeyDown += tbox_KeyDown;

        }

        public void field_Ref()
        {
            CDebug.EnterMethod("加载数据");
            try
            {
                foreach (object obj1 in lst1)
                {
                    if (obj1 is Label) ((Label)obj1).Dispose();
                    if (obj1 is Button) ((Button)obj1).Dispose();
                    if (obj1 is TextBox) ((TextBox)obj1).Dispose();
                }
                panel2.Visible = false;

                DataColumnCollection dc = ds1.Tables[0].Columns;

                #region 获取主键

                DataColumn[] dc_Key = ds1.Tables[0].PrimaryKey;
                tabKey = new TableKey[dc_Key.Length];
                strSQL_Key = "";
                if (dc_Key.Length > 0)
                {
                    for (int i = 0; i < dc_Key.Length; i++)
                    {
                        tabKey[i].dc = dc_Key[i];
                        if (ds1.Tables[0].Rows.Count > 0)
                        {
                            DataRow dr1 = ds1.Tables[0].Rows[i_RowIndex];
                            for (int k = 0; k < dc.Count; k++)
                            {
                                if (tabKey[i].dc.Caption == ds1.Tables[0].Columns[k].Caption)
                                {
                                    tabKey[i].s_Val = dr1[k].ToString();
                                }
                            }
                        }
                        else
                        {
                            if (tabKey[i].dc.DataType == typeof(Int32))
                                tabKey[i].s_Val = "0";
                            else if (tabKey[i].dc.DataType == typeof(DateTime))
                                tabKey[i].s_Val = "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'";
                            else
                                tabKey[i].s_Val = "''";
                        }
                    }
                }
                else
                {

                    tabKey = new TableKey[dc.Count];
                    for (int i = 0; i < dc.Count; i++)
                    {
                        tabKey[i].dc = dc[i];
                        if (ds1.Tables[0].Rows.Count > 0)
                        {
                            DataRow dr1 = ds1.Tables[0].Rows[i_RowIndex];
                            tabKey[i].s_Val = dr1[i].ToString();
                        }
                        else
                        {
                            tabKey[i].s_Val = "";
                        }
                    }
                }

                for (int i = 0; i < tabKey.Length; i++)
                {
                    if (i > 0 && strSQL_Key != "") strSQL_Key += s_Enter + " AND ";

                    if (tabKey[i].s_Val != "")
                    {
                        if (tabKey[i].dc.DataType == typeof(Int32))
                            strSQL_Key += tabKey[i].dc.Caption + "=" + tabKey[i].s_Val;
                        else
                            strSQL_Key += tabKey[i].dc.Caption + "='" + tabKey[i].s_Val + "'";
                    }

                }

                if (strSQL_Key == "") strSQL_Key = "1=1";

                #endregion

                #region 创建文本框

                int i_X = 0;
                int i_Y = 0;
                int i_h = 0;

                if (ds1.Tables[0].Rows.Count > 0)
                {
                    dr = ds1.Tables[0].Rows[i_RowIndex];
                }
                else
                {
                    dr = null;
                }

                int j = 0;
                i_X = 350;

                int i_ColCount = dc.Count;
                lab = new Label[i_ColCount];
                tbox = new TextBox[i_ColCount];

                for (int i = 0; i < i_ColCount; i++)
                {
                    i_Y = 25 * i_h + 15;
                    if (i_Y + 30 > panel2.Height)
                    {
                        i_h = 0;
                        i_Y = 25 * i_h + 10;
                        j++;
                    }
                    i_h++;

                    lab[i] = new Label();
                    lab[i].Text = dc[i].Caption;
                    lab[i].Left = 10 + (i_X * j);
                    lab[i].Top = i_Y;
                    lab[i].Parent = panel2;
                    lst1.Add(lab[i]);

                    tbox[i] = new TextBox();
                    tbox[i].Left = 120 + (i_X * j);
                    tbox[i].Top = i_Y;
                    tbox[i].Width = 200;
                    tbox[i].Parent = panel2;
                    if (dr != null)
                    {
                        tbox[i].Text = dr[i].ToString();
                    }
                    lst1.Add(tbox[i]);
                }

                #endregion

                #region 生成按钮

                string[] sCap = new string[] { "add", "edit", "delete", "refresh" };
                for (int i = 0; i < 4; i++)
                {
                    Button btn = new Button();
                    btn.Tag = 10 + i;
                    btn.Top = 10;
                    btn.Left = i * 100 + 50;
                    btn.Width = 80;
                    btn.Text = sCap[i];
                    btn.Parent = panel4;
                    btn.Click += btn_Click;
                    lst1.Add(btn);
                }

                #endregion

                panel2.Visible = true;

                tbox_Sql_Add.Text = getSQL_Insert();
                tbox_Sql_Edt.Text = getSQL_Update();
                tbox_Sql_Del.Text = getSQL_Delete();
                tbox_Sql_Sel.Text = getSQL_Select();

            }
            catch (System.Exception ex)
            {
                CDebug.SendError(ex.ToString());
                CDebug.Send("生成数据显示界面出错", ex);
            }

            CDebug.ExitMethod("加载数据");
        }

        private void btn_Click(object sender, EventArgs e)
        {
            int i_btn = 0;

            if (sender is Int32) i_btn = Convert.ToInt32(sender);
            if (sender is MenuItem) i_btn = Convert.ToInt32(((MenuItem)sender).Tag);
            if (sender is ToolStripMenuItem) i_btn = Convert.ToInt32(((ToolStripMenuItem)sender).Tag);
            if (sender is Button) i_btn = Convert.ToInt32(((Button)sender).Tag);
            if (sender is ToolStripButton) i_btn = Convert.ToInt32(((ToolStripButton)sender).Tag);


            string strSQL = "";
            try
            {
                switch (i_btn)
                {
                    case 1:     //fill TreeView


                        break;
                    case 2:
                        strSQL = textBox_Menu[tabCtrlSQL.SelectedIndex].SelectedText;
                        if (strSQL == "")
                        {
                            if (tabCtrlSQL.SelectedIndex == 0) strSQL = tbox_Sql_Add.Text;
                            else if (tabCtrlSQL.SelectedIndex == 1) strSQL = tbox_Sql_Edt.Text;
                            else if (tabCtrlSQL.SelectedIndex == 2) strSQL = tbox_Sql_Del.Text;
                            else if (tabCtrlSQL.SelectedIndex == 3) strSQL = tbox_Sql_Sel.Text;
                        }

                        if (CPub.MsgBox("确定要执行 [" + strSQL + "] 吗?", "执行") == true)
                            ado1.cmd1(strSQL);

                        break;
                    case 10:        //add node

                        strSQL = getSQL_Insert();
                        tbox_Sql_Add.Text = strSQL;
                        if (CPub.MsgBox(strSQL + "\n确定要添加新的数据到数据库吗？", "添加"))
                        {
                            if (sTabName == "")
                                MessageBox.Show("表名不能为空");
                            else
                                ado1.cmd1(strSQL);
                        }
                        break;
                    case 11:        //edit node
                        strSQL = getSQL_Update();
                        tbox_Sql_Add.Text = strSQL;
                        if (CPub.MsgBox(strSQL + "\n确定要写入到数据库吗？", "修改"))
                        {
                            if (sTabName == "")
                                MessageBox.Show("表名不能为空");
                            else
                                ado1.cmd1(strSQL);
                        }
                        break;
                    case 12:       //delete node
                        strSQL = getSQL_Delete();
                        tbox_Sql_Add.Text = strSQL;
                        if (CPub.MsgBox(strSQL + "\n确定要写入到数据库吗？", "删除"))
                        {
                            if (sTabName == "")
                                MessageBox.Show("表名不能为空");
                            else
                                ado1.cmd1(strSQL);
                        }
                        break;
                    case 13:        //refresh node
                        sql_Ref();
                        break;

                }
            }
            catch (System.Exception ex)
            {
                CDebug.SendError(ex.ToString());
                CDebug.Send(ex);
                MessageBox.Show(ex.ToString());
            }

        }

        public void sql_Ref()
        {
            tbox_Sql_Add.Text = getSQL_Insert();
            tbox_Sql_Edt.Text = getSQL_Update();
            tbox_Sql_Del.Text = getSQL_Delete();
            tbox_Sql_Sel.Text = getSQL_Select();
        }

        /// <summary>
        /// 在菜单中添加新记录
        /// </summary>
        /// <returns></returns>
        public string getSQL_Insert()
        {
            string strSQL_Add = "";
            string sCol = "";
            string sVal = "";

            for (int i = 0; i < tbox.Length; i++)
            {
                if (dr != null && tbox[i].Text == "") continue;

                DataColumn dc = ds1.Tables[0].Columns[i];
                //CDebug.Send(dc.DataType.Name);
                try
                {
                    if (i > 0)
                    {
                        sCol += ", " + s_Enter;
                        sVal += ", " + s_Enter;
                    }

                    sCol += dc.Caption;

                    if (dc.DataType == typeof(Int32))
                    {
                        sVal += tbox[i].Text;
                        if (sVal == "") sVal = "0";
                    }
                    else if (dc.DataType == typeof(String))
                    {
                        sVal += "'" + tbox[i].Text.Replace("'", "''") + "'";
                    }
                    else
                    {
                        sVal += "'" + tbox[i].Text + "'";
                    }
                }
                catch (System.Exception ex)
                {
                    CDebug.Send(ex.ToString());
                    CDebug.SendError("向结果集中赋值时出错", ex);
                }
            }

            strSQL_Add = "INSERT INTO " + sTabName + "(" + sCol + ") VALUES (" + sVal + ")";
            return strSQL_Add;
        }

        /// <summary>
        /// 修改菜单中的记录
        /// </summary>
        /// <returns></returns>
        public string getSQL_Update()
        {
            string strSQL_Edt = "UPDATE " + sTabName + " SET " + s_Enter;
            string sCol = "";
            string sVal = "";

            for (int i = 0; i < tbox.Length; i++)
            {

                if (i > 0) strSQL_Edt += ", " + s_Enter;

                DataColumn dc = ds1.Tables[0].Columns[i];
                sCol = dc.Caption;

                if (dc.DataType == typeof(Int32))
                {
                    sVal = tbox[i].Text;
                    if (tbox[i].Text == "")
                        sVal = "0";
                }
                else if (dc.DataType == typeof(String))
                {
                    sVal = "'" + tbox[i].Text.Replace("'", "''") + "'";
                }
                else
                {
                    sVal = "'" + tbox[i].Text + "'";
                }
                strSQL_Edt += sCol + "=" + sVal;

            }

            strSQL_Edt += s_Enter + " WHERE " + strSQL_Key;
            return strSQL_Edt;
        }

        /// <summary>
        /// 删除菜单项
        /// </summary>
        /// <returns></returns>
        public string getSQL_Delete()
        {
            string strSQL_Del = "DELETE FROM " + sTabName + s_Enter + " WHERE " + strSQL_Key;
            return strSQL_Del;
        }

        /// <summary>
        /// 生成查询语句
        /// </summary>
        /// <returns></returns>
        public string getSQL_Select()
        {
            string strSQL_Sel = "SELECT ";
            string sCol = "";

            for (int i = 0; i < tbox.Length; i++)
            {

                if (i > 0) strSQL_Sel += ", " + s_Enter;

                DataColumn dc = ds1.Tables[0].Columns[i];
                sCol = dc.Caption;


                strSQL_Sel += sCol;

            }

            strSQL_Sel += " FROM " + sTabName + s_Enter + " WHERE " + strSQL_Key;
            return strSQL_Sel;
        }

        private void FDBRow_FormClosing(object sender, FormClosingEventArgs e)
        {
            e.Cancel = true;
            this.Hide();
        }

        private void cbox_Enter_CheckedChanged(object sender, EventArgs e)
        {
            if (cbox_Enter.Checked)
            {
                s_Enter = "\n";
                for (int i = 0; i < lstTextBox.Count; i++)
                {
                    lstTextBox[i].Text = lstTextBox[i].Text.Replace(",", ",\n");
                    lstTextBox[i].Text = lstTextBox[i].Text.Replace("AND", "\nAND");
                }
            }
            else
            {
                s_Enter = "";
                for (int i = 0; i < lstTextBox.Count; i++)
                {
                    lstTextBox[i].Text = lstTextBox[i].Text.Replace("\n", "");
                }
            }
            this.Refresh();

        }

        private void tbox_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.F5)
            {
                btn_Click(btnRun, null);
            }
        }

        private void FDBRow_Shown(object sender, EventArgs e)
        {

        }

        private void tabPage2_Enter(object sender, EventArgs e)
        {

        }

        private void FDBRow_KeyDown(object sender, KeyEventArgs e)
        {

        }


    }
}
